Power BI: Tracking Created vs. Closed Records Over Time (Two Approaches Compared)

tags: PowerBIDAXDataModelingDataVisualizationPerformanceOptimization

When working with Power BI, a common requirement is to track how many records (e.g., support tickets, orders, projects) are created and closed over time. A well-structured timeline dashboard helps monitor trends and identify bottlenecks in workflows.

In this post, we’ll explore two different approaches to building this type of report, explaining their pros and cons so you can choose the best method for your data.

Approach 1: Using a Separate Table for Closed Records

How It Works

This method involves splitting the dataset into two tables:

  1. Main Table (Records Table): Contains all records with both a Creation Date and, if applicable, a Closure Date.
  2. Filtered Table for Closed Records: A separate table including only records that have a Closure Date (if needed).

Steps to Implement

  1. Create a Calendar Table that spans the relevant date range.

  2. Set up relationships:

    • Connect the Calendar Table to the Records Table [Creation Date].
    • Connect the Calendar Table to the Closed Records Table [Closure Date].

  3. Create DAX measures:

    • Count of Created Records

      Records Created = COUNTROWS(FILTER(RecordsTable, NOT(ISBLANK(RecordsTable[CreationDate]))))

    • Count of Closed Records

      Records Closed = COUNTROWS(FILTER(ClosedRecords, NOT(ISBLANK(ClosedRecords[ClosureDate]))))

  4. Build the visualization:

    • Use a line chart, placing the Calendar[Date] on the X-axis.
    • Add the Records Created and Records Closed measures to the Y-axis.

Pros & Cons

✅ Pros❌ Cons
Simple to implementRequires creating an extra table
Performance is good for medium datasetsCan introduce redundancy in the data model
Relationships are straightforwardNot ideal for large datasets with frequent updates

Approach 2: Using a Single Table with USERELATIONSHIP

How It Works

Instead of creating a separate table, this approach keeps everything in one dataset and establishes two relationships between the Calendar Table and the Records Table:

  • Active relationship with Creation Date.
  • Inactive relationship with Closure Date, activated dynamically using USERELATIONSHIP in DAX.

Steps to Implement

  1. Create a Calendar Table.

  2. Set up relationships:

    • Active: Calendar[Date] → RecordsTable[CreationDate]
    • Inactive: Calendar[Date] → RecordsTable[ClosureDate]

  3. Create DAX measures:

    • Count of Created Records

      Records Created = COUNTROWS(FILTER(RecordsTable, NOT(ISBLANK(RecordsTable[CreationDate]))))

    • Count of Closed Records (Activating the inactive relationship)

      Records Closed = CALCULATE(
    COUNTROWS(RecordsTable), 
    NOT(ISBLANK(RecordsTable[ClosureDate])),
    USERELATIONSHIP(Calendar[Date], RecordsTable[ClosureDate])
)

  4. Build the visualization:

    • Use a line chart with Calendar[Date] as the X-axis.
    • Add the Records Created and Records Closed measures to the Y-axis.

Pros & Cons

✅ Pros❌ Cons
No need for a separate tableUSERELATIONSHIP can slow down large datasets
Cleaner data modelMore advanced DAX, harder to debug
Works well with date-based filteringNot ideal for very complex models with multiple date fields

Which Approach Should You Use?

ScenarioBest Approach
Small/Medium dataset, simple modelSeparate Closed Records Table
Large dataset, need to reduce redundancySingle Table with USERELATIONSHIP
Need for easy relationships & maintenanceSeparate Closed Records Table
Need more flexibility & less duplicationSingle Table with USERELATIONSHIP

Final Thoughts

Both approaches are valid, and the choice depends on your dataset size, model complexity, and performance needs.

  • If you prefer simplicity, go with the Separate Closed Records Table approach.
  • If you want a cleaner model, use USERELATIONSHIP but be mindful of performance.

Which method do you prefer? Let me know in the comments! 🚀